using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using SqlSugar;
using SqlInjectionScanner.Config;
using SqlInjectionScanner.Models;
using SqlInjectionScanner.Utils;

namespace SqlInjectionScanner.Core
{
    /// <summary>
    /// 数据库扫描器
    /// </summary>
    public class DatabaseScanner
    {
        private readonly DatabaseConfig _config;
        private readonly ConnectionManager _connectionManager;
        private readonly InjectionDetector _injectionDetector;
        private readonly ScanStatistics _statistics;
        private readonly SqlSugarDbContext _db;

        public DatabaseScanner(DatabaseConfig config)
        {
            _config = config ?? throw new ArgumentNullException(nameof(config));
            _db = new SqlSugarDbContext(config);
            _connectionManager = new ConnectionManager(_config);
            var configuration = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json")
                .Build();
            _injectionDetector = new InjectionDetector(configuration);
            _statistics = new ScanStatistics
            {
                StartTime = DateTime.Now,
                IssuesByRiskLevel = new Dictionary<RiskLevel, int>
                {
                    { RiskLevel.Low, 0 },
                    { RiskLevel.Medium, 0 },
                    { RiskLevel.High, 0 },
                    { RiskLevel.Critical, 0 }
                },
                IssuesByType = new Dictionary<InjectionType, int>
                {
                    { InjectionType.SqlInjection, 0 },
                    { InjectionType.XssInjection, 0 },
                    { InjectionType.CommandInjection, 0 },
                    { InjectionType.Unknown, 0 }
                }
            };
        }

        /// <summary>
        /// 开始扫描
        /// </summary>
        /// <returns>扫描结果列表</returns>
        public async Task<(List<ScanResult> Results, ScanStatistics Statistics)> ScanAsync()
        {
            var results = new List<ScanResult>();

            try
            {
                // 测试连接
                if (!_connectionManager.TestConnection())
                {
                    Logger.Error("无法连接到数据库，请检查连接字符串");
                    return (results, _statistics);
                }

                using var connection = _connectionManager.CreateConnection();

                // 获取数据库名称
                string databaseName = _config.DatabaseName ?? await GetCurrentDatabaseNameAsync(connection);
                _statistics.DatabaseCount = 1;

                Logger.Information("开始扫描数据库: {DatabaseName}", databaseName);

                // 获取表列表
                var tables = await GetTablesAsync(connection, databaseName);
                _statistics.TableCount = tables.Count;

                Logger.Information("找到 {TableCount} 个表", tables.Count);

                // 扫描每个表
                foreach (var table in tables)
                {
                    var tableResults = await ScanTableAsync(connection, databaseName, table);
                    results.AddRange(tableResults);
                }

                // 更新统计信息
                _statistics.SuspiciousRecordCount = results.Count;
                foreach (var result in results)
                {
                    _statistics.IssuesByRiskLevel[result.RiskLevel]++;
                    _statistics.IssuesByType[result.InjectionType]++;
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "扫描过程中发生错误");
            }
            finally
            {
                _statistics.EndTime = DateTime.Now;
            }

            return (results, _statistics);
        }

        /// <summary>
        /// 获取当前数据库名称
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <returns>数据库名称</returns>
        private async Task<string> GetCurrentDatabaseNameAsync(DbConnection connection)
        {
            try
            {
                return await _db.Db.Ado.GetStringAsync("SELECT DATABASE()", new { });
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "获取当前数据库名称时发生错误");
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取表列表
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="databaseName">数据库名称</param>
        /// <returns>表名列表</returns>
        private async Task<List<string>> GetTablesAsync(DbConnection connection, string databaseName)
        {
            try
            {
                var tables = _db.Db.DbMaintenance.GetTableInfoList(false);
                var tableList = tables.Select(t => t.Name).ToList();

                // 过滤表
                if (_config.TableNames != null && _config.TableNames.Count > 0)
                {
                    tableList = tableList.Where(t => _config.TableNames.Contains(t, StringComparer.OrdinalIgnoreCase)).ToList();
                }

                return tableList;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取表列表时出错: {ex.Message}");
                return new List<string>();
            }
        }

        /// <summary>
        /// 扫描表
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="databaseName">数据库名称</param>
        /// <param name="tableName">表名</param>
        /// <returns>扫描结果列表</returns>
        private async Task<List<ScanResult>> ScanTableAsync(DbConnection connection, string databaseName, string tableName)
        {
            var results = new List<ScanResult>();

            try
            {
                Logger.Information("扫描表: {TableName}", tableName);

                // 获取表结构
                var columns = await GetTableColumnsAsync(connection, databaseName, tableName);
                _statistics.ColumnCount += columns.Count;

                // 获取主键列
                string? primaryKeyColumn = await GetPrimaryKeyColumnAsync(connection, databaseName, tableName);

                // 过滤出文本类型的列
                var textColumns = columns.Where(c => IsTextColumn(c.DataType)).ToList();

                if (textColumns.Count == 0)
                {
                    Logger.Information("表 {TableName} 没有文本类型的列，跳过", tableName);
                    return results;
                }

                // 使用SQLSugar构建查询
                var columnsToSelect = textColumns.Select(c => c.ColumnName).ToList();
                if (primaryKeyColumn != null && !textColumns.Any(c => c.ColumnName.Equals(primaryKeyColumn, StringComparison.OrdinalIgnoreCase)))
                {
                    columnsToSelect.Add(primaryKeyColumn);
                }
                // Replace the following line:  
                // var query = _db.Db.QueryableByString(tableName)  

                // With this corrected line:  
                var query = _db.Db.Queryable<object>().AS(tableName);

                // 分批查询数据
                int offset = 0;
                int totalRecords = 0;

                while (offset < _config.MaxRecordsPerTable && offset >= 0)
                {
                    var data = await query.Skip(offset).Take(_config.BatchSize).ToListAsync();

                    if (data == null || data.Count == 0)
                        break;

                    int recordCount = 0;
                    foreach (var row in data)
                    {
                        recordCount++;
                        totalRecords++;

                        foreach (var column in textColumns)
                        {
                            if (row == null)
                                continue;

                            // 尝试获取属性值，处理动态对象和大小写问题
                            string? value = null;
                            try
                            {
                                // 处理ExpandoObject情况
                                if (row is System.Dynamic.ExpandoObject expando)
                                {
                                    var dict = (IDictionary<string, object>)expando;
                                    if (dict.TryGetValue(column.ColumnName, out var val))
                                    {
                                        value = val?.ToString();
                                    }
                                }
                                // 处理普通对象情况
                                else
                                {
                                    var prop = row.GetType().GetProperties()
                                        .FirstOrDefault(p => p.Name.Equals(column.ColumnName, StringComparison.OrdinalIgnoreCase));
                                    
                                    value = prop?.GetValue(row)?.ToString();
                                }
                            }
                            catch
                            {
                                continue;
                            }
                            
                            if (string.IsNullOrEmpty(value))
                                continue;

                            var matchedPattern = _injectionDetector.DetectInjection(value);
                            if (matchedPattern != null)
                            {
                                var result = new ScanResult
                                {
                                    DatabaseName = databaseName,
                                    TableName = tableName,
                                    ColumnName = column.ColumnName,
                                    DataType = column.DataType,
                                    SuspiciousValue = value,
                                    MatchedPattern = matchedPattern,
                                    ScanTime = DateTime.Now
                                };

                                // 添加主键信息（如果有）
                                if (primaryKeyColumn != null && row.GetType().GetProperty(primaryKeyColumn)?.GetValue(row) != null)
                                {
                                    result.PrimaryKeyColumn = primaryKeyColumn;
                                    result.PrimaryKeyValue = row.GetType().GetProperty(primaryKeyColumn)?.GetValue(row)?.ToString();
                                }

                                // 生成修复建议
                                result.RemediationAdvice = _injectionDetector.GenerateRemediationAdvice(result);

                                results.Add(result);

                                Logger.Warning("在表 {TableName} 的列 {ColumnName} 中发现可疑值: {Value}",
                                    tableName, column.ColumnName, value);
                            }
                        }
                    }

                    // 如果返回的记录数小于批处理大小，说明已经没有更多数据了
                    if (recordCount < _config.BatchSize)
                        break;

                    offset += _config.BatchSize;
                }

                _statistics.RecordCount += totalRecords;
                Logger.Information("表 {TableName} 扫描完成，共检查 {RecordCount} 条记录，发现 {SuspiciousCount} 个可疑值",
                    tableName, totalRecords, results.Count);
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "扫描表 {TableName} 时发生错误", tableName);
            }

            return results;
        }

        /// <summary>
        /// 获取表的列信息
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="databaseName">数据库名称</param>
        /// <param name="tableName">表名</param>
        /// <returns>列信息列表</returns>
        private async Task<List<(string ColumnName, string DataType)>> GetTableColumnsAsync(DbConnection connection, string databaseName, string tableName)
        {
            try
            {
                var columns = _db.Db.DbMaintenance.GetColumnInfosByTableName(tableName, false);
                return columns.Select(c => (c.DbColumnName, c.DataType)).ToList();
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "获取表 {TableName} 的列信息时发生错误", tableName);
                return new List<(string, string)>();
            }
        }

        /// <summary>
        /// 获取表的主键列
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="databaseName">数据库名称</param>
        /// <param name="tableName">表名</param>
        /// <returns>主键列名</returns>
        private async Task<string?> GetPrimaryKeyColumnAsync(DbConnection connection, string databaseName, string tableName)
        {
            try
            {
                var columns = _db.Db.DbMaintenance.GetColumnInfosByTableName(tableName, false);
                return columns.FirstOrDefault(c => c.IsPrimarykey)?.DbColumnName;
            }
            catch (Exception ex)
            {
                Logger.Warning("获取表 {TableName} 的主键列时发生错误: {Exception}", tableName, ex);
                return null;
            }
        }

        /// <summary>
        /// 判断列是否为文本类型
        /// </summary>
        /// <param name="dataType">数据类型</param>
        /// <returns>是否为文本类型</returns>
        private bool IsTextColumn(string dataType)
        {
            var textTypes = new[]
            {
                "char", "varchar", "text", "nchar", "nvarchar", "ntext",
                "clob", "longtext", "mediumtext", "tinytext", "character varying",
                "json", "jsonb", "xml"
            };

            return textTypes.Any(t => dataType.ToLower().Contains(t));
        }

        /// <summary>
        /// 获取带架构的表名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>带架构的表名</returns>
        private string GetTableNameWithSchema(string tableName)
        {
            // 对于某些数据库类型，可能需要添加引号或其他修饰符
            return _config.DatabaseType switch
            {
                "SqlServer" => $"[{tableName}]",
                "MySql" => $"`{tableName}`",
                "PostgreSql" => $"\"{tableName}\"",
                _ => tableName
            };
        }

        /// <summary>
        /// 获取分页查询SQL (已弃用，使用SQLSugar分页机制替代)
        /// </summary>
        private string GetPaginatedQuery(string baseSql, int offset, int limit)
        {
            throw new NotImplementedException("请使用SQLSugar的分页机制替代此方法");
        }

        /// <summary>
        /// 获取详细统计信息
        /// </summary>
        /// <param name="results">扫描结果列表</param>
        /// <returns>包含详细统计信息的对象</returns>
        public object GetStatistics(List<ScanResult> results)
        {
            return new
            {
                // 基础统计
                TotalDatabases = _statistics.DatabaseCount,
                TotalTables = _statistics.TableCount,
                TotalColumns = _statistics.ColumnCount,
                TotalRecords = _statistics.RecordCount,
                TotalIssues = _statistics.SuspiciousRecordCount,
                ScanDuration = _statistics.EndTime - _statistics.StartTime,
                
                // 按风险等级统计
                IssuesByRiskLevel = _statistics.IssuesByRiskLevel
                    .OrderByDescending(x => x.Value)
                    .ToDictionary(x => x.Key, x => x.Value),
                
                // 按注入类型统计
                IssuesByType = _statistics.IssuesByType
                    .OrderByDescending(x => x.Value)
                    .ToDictionary(x => x.Key, x => x.Value),
                
                // 按表统计
                IssuesByTable = results
                    .GroupBy(r => r.TableName)
                    .OrderByDescending(g => g.Count())
                    .ToDictionary(g => g.Key, g => g.Count()),
                
                // 按列统计
                IssuesByColumn = results
                    .GroupBy(r => $"{r.TableName}.{r.ColumnName}")
                    .OrderByDescending(g => g.Count())
                    .ToDictionary(g => g.Key, g => g.Count()),
                
                // 高风险值示例
                HighRiskExamples = results
                    .Where(r => r.RiskLevel == RiskLevel.High || r.RiskLevel == RiskLevel.Critical)
                    .Take(5)
                    .Select(r => new 
                    {
                        r.TableName, 
                        r.ColumnName,
                        r.SuspiciousValue,
                        r.RiskLevel
                    })
            };
        }
    }
}